<<<<<<< HEAD <<<<<<< HEAD ======= >>>>>>> master ======= >>>>>>> master GIS-Clinical Collaboration: Webpage-based Report

The Ask

DCPO Halawa and Data Analyst Loftus asked if it would be possible to use Cook County Medical Examiner Office data, in addition to internal sources, to measure shooting incidents involving Department invovled youth. While that question was answered in another document, the full capability of these analytic tools could not be demonstrated due to security concerns regarding department data. This report is an attempt to more completely showcase the possibilities of GIS powered dashboards and reports.

The Lift

The requirements for this report have not changed significantly as the following steps are still required:

Importing the Data

Importing the medical examiner’s (ME) office data is simple: The Cook County Open Data website has a link that when incorporated into code of the analysis, allows for easy analysis. This data is updated daily, and because of the link, no file needs to downloaded then uploaded to the analytic code. In short, powering on the report loads the most current data into the analysis.

Cleaning Data

Even though this data is coming from directly from the ME office’s database, cleaning will still need to be done. The data is already fairly tidy, but they are organized by the rules establisehd by the ME, and contains variables that are not – as of this writing – of interest to the department:

<<<<<<< HEAD <<<<<<< HEAD Medical Examiner’s Office Data: 03/07/2019 ======= Medical Examiner’s Office Data: 11/05/2019 >>>>>>> master ======= Medical Examiner’s Office Data: 11/06/2019 >>>>>>> master
casenumber incident_date death_date age gender race latino manner primarycause gunrelated opioids incident_street longitude latitude objectid location.type location.coordinates cold_related heat_related residence_zip incident_zip secondarycause primarycause_lineb primarycause_linec residence_city incident_city
ME2014-00627 1411311600 1411325400 25 Male Black FALSE UNDETERMINED DROWNING FALSE FALSE 499 E. North Water St.  -87.61544627360976 41.889496395313714 48783 Point c(-87.6154462736098, 41.8894963953137) NO NO 60608 NA NA NA NA NA NA
ME2014-00649 1411422000 1411422900 61 Male Black FALSE NATURAL HYPERTENSIVE ARTERIOSCLEROTIC CARDIOVASCULAR DISEASE FALSE FALSE 1317 PORTLAND -87.6232147232388 41.50732804103343 48784 Point c(-87.6232147232388, 41.5073280410334) NO NO 60411 60411 NA NA NA NA NA
ME2014-00712 1411855080 1411855800 51 Male White FALSE NATURAL DIABETIC KETOACIDOSIS FALSE FALSE 8759 PELIKIN -87.84975118706316 41.77358560899294 48785 Point c(-87.8497511870632, 41.7735856089929) NO NO 60525 60525 NA NA NA NA NA
ME2014-00792 1412226000 1412277300 55 Male Black FALSE NATURAL HYPERTENSIVE AND ARTERIOSCLEROTIC CARDIOVASCULAR DISEASE FALSE FALSE 7750 South Emerald -87.64324736907044 41.75285594967308 48786 Point c(-87.6432473690704, 41.7528559496731) NO NO NA NA NA NA NA NA NA

The steps for this data set, then, include removing those variables that are of no interest and filtering results to focus on the age-range of interest to the department. Additionally, the way the ME classify’s race is complicated. Race is listed as Black, White, Other, Asian, NA, Unknown, Am. Indian. Latino is listed as a seperate factor. For the department’s needs, the ME’s data needs to add Latino to race. This is a simple cleaning function in R, that creates the follow categories: Black, White, Other, Asian, NA, Unknown, White Latino, Latino, Am. Indian, Black Latino, NA Latino, Unknown Latino, Am. Indian Latino, Asian Latino

Visualizing the Data

The cleaned ME dataset has following 27 columns: casenumber, incident_date, death_date, age, gender, race, latino, manner, primarycause, gunrelated, opioids, incident_street, longitude, latitude, objectid, location.type, location.coordinates, cold_related, heat_related, residence_zip, incident_zip, secondarycause, primarycause_lineb, primarycause_linec, residence_city, incident_city, source. Displaying all of this data in a single column is not useful on paper, but because this report is web based, we can add scroll bars to the table in order to view all the entries.

<<<<<<< HEAD <<<<<<< HEAD Medical Examiner’s Office Data 03/07/2019 ======= Medical Examiner’s Office Data 11/05/2019 >>>>>>> master ======= Medical Examiner’s Office Data 11/06/2019 >>>>>>> master
casenumber incident_date death_date age gender race latino manner primarycause gunrelated opioids incident_street longitude latitude objectid location.type location.coordinates cold_related heat_related residence_zip incident_zip secondarycause primarycause_lineb primarycause_linec residence_city incident_city source
ME2017-02502 1496301060 1496356080 19 Male Black FALSE HOMICIDE GUNSHOT WOUND OF HEAD TRUE FALSE intersection of andover and revere NA NA 49395 NA NULL NO NO 60411 60411 NA NA NA Sauk Village CHICAGO HEIGHTS Medical Examiner
ME2017-00331 1484773200 1484867640 19 Male Black FALSE HOMICIDE GUNSHOT WOUND OF TORSO TRUE FALSE 4200 GEORGIA STREET NA NA 49479 NA NULL NO NO 46410 46409 NA NA NA Merrillville GARY Medical Examiner
ME2017-02072 1494136800 1494170100 21 Male White FALSE SUICIDE GUNSHOT WOUND OF HEAD TRUE FALSE 1860 S ARLINGTON HTS RD -87.98399339672822 42.05188610113801 49481 Point c(-87.9839933967282, 42.051886101138) NO NO 60067 60005 NA NA NA Palatine ARLINGTON HEIGHTS Medical Examiner
ME2017-02406 1495901700 1495903620 16 Male Black FALSE HOMICIDE GUNSHOT WOUNDS OF THE CHEST TRUE FALSE 11 N. Madion Street -87.77516605946164 41.87996258959737 49585 Point c(-87.7751660594616, 41.8799625895974) NO NO 60612 60302 NA NA NA Chicago OAK PARK Medical Examiner
ME2017-00581 1486167600 1486169280 26 Male White Latino TRUE HOMICIDE GUNSHOT WOUND OF CHEST TRUE FALSE MAPLE EAST OF JACKSON NA NA 49589 NA NULL NO NO 60139 60303 NA NA NA Glendale Heights OAK PARK Medical Examiner
ME2017-05649 1512277140 1512278940 23 Male Black FALSE HOMICIDE MULTIPLE GUNSHOT WOUNDS TRUE FALSE 7201 W. NORTH AVE -87.80634190943427 41.90857041261212 49590 Point c(-87.8063419094343, 41.9085704126121) NO NO NA 60305 NA NA NA NA RIVER FOREST Medical Examiner
ME2017-00242 1484365440 1484404200 20 Male Black FALSE HOMICIDE GUNSHOT WOUND OF HEAD TRUE FALSE S Orchard Rd And Cornell Ave NA NA 49678 NA NULL NO NO 60506 60538 NA NA NA Aurora MONTGOMERY Medical Examiner
ME2017-00795 1486608120 1487179200 25 Male Black FALSE HOMICIDE GUNSHOT WOUND OF BACK TRUE FALSE 345 S Canal St -87.63962853261914 41.888229470064914 49692 Point c(-87.6396285326191, 41.8882294700649) NO NO 55443 60606 NA NA NA Minneapolis CHICAGO Medical Examiner
ME2017-03878 1503212280 1503213660 22 Male Black FALSE HOMICIDE MULTIPLE GUNSHOT WOUNDS TRUE FALSE 1042 WEST MAXWELL -87.6526164063243 41.864741147421995 49753 Point c(-87.6526164063243, 41.864741147422) NO NO 60608 NA NA NA NA Chicago CHICAGO Medical Examiner
ME2017-00986 1488093540 1488097620 18 Male White Latino TRUE HOMICIDE GUNSHOT WOUND OF BACK TRUE FALSE 10942 S MACKINAW -87.54307994556501 41.696109911920374 49789 Point c(-87.543079945565, 41.6961099119204) NO NO 60617 60617 NA NA NA Chicago CHICAGO Medical Examiner

A more useful way of displaying the data is summarizing by categories:

2017 Shooting Data Summarized
manner race gender Total
HOMICIDE Black Female 26
HOMICIDE Black Male 303
HOMICIDE Black Latino Male 2
HOMICIDE Latino Male 2
HOMICIDE Other Male 3
HOMICIDE White Female 1
HOMICIDE White Male 6
HOMICIDE White Latino Female 5
HOMICIDE White Latino Male 65
SUICIDE Black Male 13
SUICIDE White Female 4
SUICIDE White Male 11
SUICIDE White Latino Female 1
SUICIDE White Latino Male 4
UNDETERMINED Black Male 2

And by plotting the data.

<<<<<<< HEAD <<<<<<< HEAD

In short, the data can be filtered, selected and summarized for descriptive statistics, or displayed as a variety of graphs (bar, line, bubble, etc.) With additional time and resources, this data can be combined with the department’s internal data for more analysis and insight. Furthermore, additional time and web formating will allow for interactive graphs that would allow individual users to analyze the data in a variety of ways.

=======

=======

>>>>>>> master

In short, the data can be selected for visualization, aggregated for totals, or displayed as a bar graph. With additional time and resources, this data can be combined with the department’s internal data for more analysis and insight.

>>>>>>> master

GIS

Because this report is a website, the static GIS maps are interactive. For the purposes of this report,the visualizations are relatively static – It is possible to zoom in on particular neighborhoods and have pop-ups on points – but versions of these maps could be made with user-based selection and filter criteria.

ME Shooting: Incidents by Cook County Commissioner District

<<<<<<< HEAD <<<<<<< HEAD

Cook County Shooting Incidents 2016 - Today. Source Cook County Medical Examiner’s Office 

## Warning in pal(v): Some values were outside the color scale and will be
## treated as NA
=======
>>>>>>> master =======
>>>>>>> master

Medical Examiner

A note about the data sets: The ME data was initially downloaded and cleaned before mapped. Their records were missing 16 location entries. In a more formal report, these missing records would be more thoroughly examined. For this proof of concept, they were simply removed from the data set.

These maps are not limited to yearly intervals: Chloropleths can be based on daily, monthly, or quarterly dates. The restrictions of this level of detail is not the amount of work, but where the data will be displayed. This report was written as a webpage and printed to PDF because that level of work is relatively easy. If this report was a standard report, then in addition to being a web page, it would also be printable as a PDF. However, another option would be the creation of a dashboard.

Displaying Reports

Using the data and methods contained in this document, creating a GIS-informed report that runs regularly would be a relatively simple task. Creating a dashboard or app to display the same data would only be marginally more intensive. The most pressing concern about a dashboard/app would be getting permission from County stakeholders to approve the use of an internal web server.

The advantages of a dashboard/app would be the ability to filter the data sets and display them in real time, instead of “hard coding” results like this report. This method would increase collaboration, free up staff time for other analysis, generate less paper, and allow for easy access for all OCJ staff. A note about CFive. They may, or may not, have this capacity built in a future build. This report is not currently contained within the Statement of Work and CFive uses different methods for visualizations. Additionally, to date we have yet to see any of their forms or reports.

A dashboard of the ME’s data can be prototyped within a few weeks. Allyson’s data cannot be displayed this way unless additional steps are taken to protect the identity of court-involved youth.

Recap and Next Steps

The data provided by Allyson can be plotted onto a map of the city or county to reveal counts of shooting incidents. That requirement was met through the creation of this report. Minor tweaks to collection, cleaning, and visualizing need to be completed in order to ensure information integrity, and that process can begin when it is approved. ME office data is also easily accessed and visualized; furthermore, combining both data sets can be done in order to analyze gaps from either source.

The next step for this ask is determining if the Department wants to focus on the development of regular, programmable reports like this draft or developing an app/dashboard. Both require roughly the same amount of work. A dashboard/app will require additional permissions from the Office of the Chief Judge and the Bureau of Technology, but this is the primary barrier to this method.

A prototype of this system can be built in a matter of weeks; however, due to the public nature of this measure, the internally collected data would need to be scrubbed of any identifying information in order to protect the identity of court involved youth. The value of this prototype, beyond a proof of concept, would be the ability to analyze a key metric for the court without having to use Excel, R, or any other data analysis tool.